"""
Case Type   : 系统视图PG_PREPARED_STATEMENT
Case Name   : 用户权限改变后查询函数，权限控制正确
Create At   : 2023/12/11
Owner       : ningyali
Description :
    1.建表
    2.新建session1，创建预备语句
    3.新建session2，创建预备语句
    4.创建用户u_test
    5.循环执行以下步骤
        (1)分别赋予u_test monadmin,sysadmin,poladmin,createrole,
            auditadmin,opradmin权限
        (2)使用u_test查询视图
        (3)取消u_test的权限
        (4)使用u_test查询视图
    6.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功
    5.执行成功，只有monadmin,sysadmin权限可查询到stmt1,stmt2
    6.执行成功
History     :
"""

import os
import time
import unittest

from yat.test import macro

from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class PgPreparedStatement27(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_prepare_27'
        self.prepare_name = 'stmt'
        self.u_name = 'u_prepare_27'
        self.prepare_sql = f"prepare {self.prepare_name} as " \
            f"insert into {self.t_name} values(1, 'openGauss');" \
            f"select pg_sleep(60);"
        self.query_sql = 'select * from pg_prepared_statement(0) ' \
                         'order by 1, 2;'
        self.query_sql += f"select pg_prepared_statement(sessionid) " \
            f"from pg_stat_activity;"
        self.q_cmd = f'source {macro.DB_ENV_PATH};' \
            f'gsql -d {self.pri_sh.node.db_name} -U {self.u_name} ' \
            f'-W "{macro.COMMON_PASSWD}" -c "{self.query_sql}"'
        self.range_params = [{'perm_key': 'monadmin',
                              'expect': '执行成功',
                              'q_expect': '执行成功，可查询到stmt1,stmt2'},
                             {'perm_key': 'sysadmin',
                              'q_expect': '执行成功，可查询到stmt1,stmt2'},
                             {'perm_key': 'poladmin',
                              'q_expect': '连接成功，查询失败，提示不允许调用'},
                             {'perm_key': 'createrole',
                              'q_expect': '连接成功，查询失败，提示不允许调用'},
                             {'perm_key': 'auditadmin',
                              'q_expect': '连接成功，查询失败，提示不允许调用'},
                             {'perm_key': 'opradmin',
                              'q_expect': '连接成功，查询失败，提示不允许调用'}
                             ]
        self.err_expect = "ERROR:  permission denied for function " \
                          "pg_prepared_statements"

    def test_1(self):
        text = 'step1:建表; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};' \
            f'create table {self.t_name}(c1 int, c2 text);'
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, f'执行失败：{text}')

        text = 'step2:新建session1，创建预备语句; expect:执行成功'
        self.log.info(f'-----{text}-----')
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(f'{self.prepare_sql}',))
        session1.setDaemon(True)
        session1.start()

        text = 'step3:新建session2，创建预备语句; expect:执行成功'
        self.log.info(f'-----{text}-----')
        session2 = ComThread(self.pri_sh.execut_db_sql,
                             args=(f'{self.prepare_sql}',))
        session2.setDaemon(True)
        session2.start()
        time.sleep(3)

        text = f'step4:创建{self.u_name}用户; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop user if exists {self.u_name};' \
            f'create user {self.u_name} password \\"{macro.COMMON_PASSWD}\\";'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, res,
                      f'执行失败：{text}')

        for i in range(5):
            self.log.info(f'-----第{i}次执行-----')
            for item in self.range_params:
                text = f'step(1):给{self.u_name}赋予{item["perm_key"]}权限; ' \
                    f'expect:执行成功'
                self.log.info(f'-----{text}-----')
                sql = f'alter user {self.u_name} {item["perm_key"]};'
                res = self.pri_sh.execut_db_sql(sql)
                self.log.info(res)
                self.assertIn(self.constant.ALTER_ROLE_SUCCESS_MSG, res,
                              f'执行失败：{text}')

                text = f'step(2):使用{self.u_name}用户，查询视图; ' \
                    f'expect:{item["q_expect"]}'
                self.log.info(f'-----{text}-----')
                self.log.info(self.q_cmd)
                res = self.pri_sh.node.sh(self.q_cmd).result()
                self.log.info(res)
                if '可查询到' in item['q_expect']:
                    self.assertEqual(res.count(self.prepare_name), 8,
                                     f'执行失败：{text}')
                else:
                    self.assertIn(self.err_expect, res, f'执行失败：{text}')

                text = f'step(3):取消{item["perm_key"]}权限; expect:执行成功'
                self.log.info(f'-----{text}-----')
                sql = f'alter user {self.u_name} no{item["perm_key"]};'
                res = self.pri_sh.execut_db_sql(sql)
                self.log.info(res)
                self.assertIn(self.constant.ALTER_ROLE_SUCCESS_MSG, res,
                              f'执行失败：{text}')

                text = f'step(4):再次查询视图; expect:{item["q_expect"]}'
                self.log.info(f'-----{text}-----')
                self.log.info(self.q_cmd)
                res = self.pri_sh.node.sh(self.q_cmd).result()
                self.log.info(res)
                self.assertIn(self.err_expect, res, f'执行失败：{text}')

        text = '获取session1、session2结果'
        self.log.info(f'-----{text}-----')
        session1.join(180)
        session2.join(180)
        res1 = session1.get_result()
        res2 = session2.get_result()
        self.log.info(f'res1:\n{res1}')
        self.log.info(f'res2:\n{res2}')
        self.assertIn('PREPARE', res1, f'执行失败：{text}')
        self.assertIn('PREPARE', res2, f'执行失败：{text}')

    def tearDown(self):
        self.log.info('----run teardown----')
        text1 = 'step6:清理环境; expect:执行成功'
        self.log.info(f'-----{text1}-----')
        sql = f'drop table if exists {self.t_name} cascade;' \
            f'drop user if exists {self.u_name};'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text1}')
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, res,
                      f'执行失败：{text1}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
